Method for simplifying databinding in application programs

ABSTRACT

A method for communicating between an application and a database by using a lightweight stored procedure data-binding framework for applications written in object oriented programming languages such as Java, significantly reduces the coding effort required to communicate with a relational database. Once the developer specifies the needed tabular data, the inventive framework generates all needed data objects and stored procedures to accomplish the interaction with that database data. The automatically generated code is then used by the application via a run-time component. This means the developer need only make simple calls in a few lines of code for database interaction. The encapsulated data model deals only with persistence and retrieval issues and is therefore de-coupled from the business model. The developer can therefore concentrate on implementing a complex business object model. And, changes to the business object models have no direct impact on the underlying persistence model. Automatic code generation reduces a developer&#39;s coding time by up to 90%. Moreover, coding errors are virtually eliminated since the generated code is not prone to manual coding errors. Thus, testing time is reduced as well.

FIELD OF THE INVENTION

[0001] This invention relates to a method for the exchange of databetween an application program and a database using a framework, and inparticular to a method, using a databinding framework, thatautomatically generates computer code for reducing the number of linesof application code that need to be written by the developer fordatabase operations. The method is particularly useful with complexbusiness and financial application programs.

BACKGROUND OF THE INVENTION

[0002] Computer programs (applications) can be very complex. Suchapplications might perform calculations needed for financialtransactions. For example, financial applications can facilitate thebuying or selling of business or financial instruments such asderivatives. The complexity involves the number and type of calculationsperformed by the application, the large volume of data on which theapplication bases those calculations and the computed data representingthe results of the application's computations. Software developersgenerally write these applications in a high level computer programminglanguage. Object Oriented languages, such as Sun's Java and its variousversions as J2EE (the “enterprise edition”) are particularly well suitedfor very large complex applications.

[0003] The data that represents the inputs and outputs of theapplication can be voluminous. And, in large companies and financialinstitutions, it is likely that the data is shared by severalapplications. In such large and complex systems, the data representingall of the information needed by the application (inputs) and processedinformation (outputs) is generally stored on one or more databases thatare semiautonomous of the application, i.e. the application does notdirectly read and write information to the databases. It communicateswith the database through a database management program. Sybase,Microsoft SQL Server, and Oracle are exemplary of such commercialdatabase products.

[0004] One aspect of database programs is that the data is stored, read,and written in the form of tables. Database data is typically grouped intables by similar characteristics, and the database program maintainsthe relationships between the tables. For example, the characteristicsof a particular financial instrument might be found in one table whileit's trading history can be found in another table. These tables can bethen be related by the identifier of the instrument. Table baseddatabase systems are known as relational databases. Relational databaseprograms are optimized for searching, for reporting selected data fromthe database, and for writing data to selected data tables.

[0005] Communication with commercial databases is typically done via astructured query language (SQL). SQL lines can use variable inputparameters, such as the name of a financial instrument, or a date, orrange of dates. Many lines of SQL code may be required for a givendatabase operation. Stored procedures are lists of SQL code that allowfor input parameters and generate tabular result sets. The storedprocedures are generally stored in the database.

[0006] By contrast, applications are usually written in high levelobject oriented languages. Object oriented languages such as J2EE offervery powerful and computationally efficient computing environments forsolving business calculations. (The calculations are typically presentedto the application developer as a set of rules known as the businessmodel). Object Oriented languages are designed to work with objects.Objects are programming structures of the application that contain bothfunctions and the corresponding data for a given computational task.They are generally focused on solving a particular problem.

[0007] An application runs on one or more computers. During “runtime” ittypically reads and writes large volumes of information in the form ofdata. After a particular run, the program needs to save a great deal ofinformation so that the next time it runs, it continues one or morecalculations from the previous ending state. Also, some data withinobjects simply needs to be saved in the database. Persistence refers tothe ability of the application to save various conditions of theapplication and data at the end of a run so that it is available to theapplication at the beginning of the next run.

[0008] Object oriented languages are becoming ever more rich in theirability to interact with other systems and programs such as commercialdatabase programs. They do this by offering a suite of libraries thatprovide pre-written application programming interfaces (API) forapplication developers. For example J2EE offers tools to communicatewith database programs such as Sybase. Even with these tools, the Javaprogrammer still must write many lines of Java code and then many linesof SQL code (stored procedures) to perform a given database operation.Depending on the complexity of the interaction, it can literally takeweeks for a programmer to write all of the needed code for aninteraction between the application and the database, as between Javaand Sybase. And, because the stored procedures can comprise many linesof very detailed SQL code, there is a very good chance for codingerrors. Proof reading such code is difficult and time consuming.

[0009] Because the application handles information as part of itsobjects, and the database handles information as tables, the datastructures must be converted for the two programs to communicate witheach other. One approach is to provide a system that maps tabular datato objects in a way that is transparent to the programmer writing theapplication. This system of binding tabular data to objects is calleddatabinding. In databinding, objects can be written that deal only withthe information to be exchanged. Similarly, stored procedures can bewritten to only carry out the corresponding database interactions. Thestored procedures are “lightweight” in the sense that they do notperform any business calculations. A method, or framework, to managedata for an application is ideally “encapsulated”. That is, it isisolated from the application's business calculations (the businessmodel).

[0010] What is needed is a method that implements a lightweight storedprocedure framework whereby the applications programmer solving abusiness model need only specify a few simple lines of code tocommunicate with the tabular data in a relational database.

SUMMARY OF THE INVENTION

[0011] A method for communicating between an application and a databaseby using a lightweight stored procedure data-binding framework forapplications written in object oriented programming languages such asJava. The method significantly reduces the coding effort required tocommunicate with a relational database. Once the developer specifies theneeded tabular data, the inventive framework generates all needed dataobjects and stored procedures to accomplish the interaction with thatdatabase data. The automatically generated code is then used by theapplication via a run-time component. This means the developer need onlymake simple calls in a few lines of code for database interaction.

[0012] The encapsulated data model deals only with persistence andretrieval issues and is therefore de-coupled from the business model.The developer can therefore concentrate on implementing a complexbusiness object model. And, changes to the business object models haveno direct impact on the underlying persistence model. The encapsulatedmethod provides a generic configurable means of persisting andretrieving objects to and from a relational database. While it isdescribed in a Java-Sybase environment embodiment, the method isapplicable to other object oriented languages and database programs.

[0013] Automatic code generation reduces a developer's coding time by upto 90%. Moreover, coding errors are virtually eliminated since thegenerated code is not prone to manual coding errors. Thus, testing timeis reduced as well.

BRIEF DESCRIPTION OF THE DRAWINGS

[0014] The advantages, nature and various additional features of theinvention will appear more fully upon consideration of the illustrativeembodiments now to be described in detail in connection with theaccompanying drawings. In the drawings:

[0015]FIG. 1 is a simplified block diagram showing the databindingframework.

[0016]FIG. 2 is a simplified block diagram of the framework's generatorcomponent.

[0017]FIG. 3 is a simplified block of the framework's run-timecomponents.

[0018] It is to be understood that the drawings are for the purpose ofillustrating the concepts of the invention, and except for the graphs,are not to scale. It is also understood that all application code, otherframework code, the database program, and data reside on tangiblecomputer readable media and run on one or more computer systems.

DESCRIPTION

[0019] The application is divided into two parts. Part I discusses theinvention as a general framework solution for object orientedapplications that access data from database programs using a structuredquery language (SQL) and groups of SQL calls as stored procedures. PartII of the application, for those skilled in the art, is an example ofthe inventive framework for the Java J2EE and Sybase programmingenvironments.

[0020] Part I: A Lightweight Stored Procedure Data-Binding Framework ForBusiness And Financial Applications

[0021] The inventive framework comprises two distinct components. A codegenerator is used during program development to automatically generaterequired stored procedures, Java code, and an XML data exchange anddocumentation middle layer. A second component, the run-time componentintegrates the generated code into the running business application aslibrary routines so that the business program can retrieve, store, andpersist data with very minimal Java code, usually with only one or twolines of Java code.

[0022] The method of databinding database relational tables toapplication objects shown in FIG. 1. In Block A, the application isprovided and in Block B, the database. The framework discussed in PartsI and II is provided in Block C. Using the method, first databindingfiles are generated in Block D, and finally those databinding files areintegrated into the application at run-time by the run-time component ofthe framework provided in Block E.

[0023] The Code Generator of the Framework:

[0024] The code generator is shown in FIG. 2. The developer supplies alist 101 of needed data specified as database table names. By way ofexample, the developer here is interacting with table “table_one” 102.The data (information) corresponding to table_one resides in relationaldatabase 103. The generating tool initially parses the required tabulardata and generates table meta-data 104 in the form of database 102primary keys, columns, and data types.

[0025] Based on meta-data 104, the code generator creates three types ofoutput files, all of which are used later during the running of thebusiness or financial application (hereinafter “application”) via theframework's run-time component. The generated stored procedures 105 arethe part of the framework that communicate most directly with therelational database.

[0026] The generated XML files 106 create a “middle layer” of theframework. They are written in the eXtensible Markup Language (XML).These files act as the “glue” by defining what each stored procedure iscalled, and what its input and output parameters are. At run-time, theXML documents (configuration files) reside between the stored proceduresthat directly access database 102 and the Java code of the application.The XML documents can be read directly as text documents. Because XMLdocuments are structured and nested, they are also self-explanatory andself-documenting.

[0027] The code generator also writes value objects 107. The valueobjects, generated “beans” in Java, are part of an object class forexchanging data with the database. They provide the final links thatultimately map the tables of relational database 102 to the applicationwritten in an object oriented language such as Java. Following thephilosophy of frameworks created in object oriented programmingenvironments, the value objects perform only functions related toinformation exchange between database 102 and the application. Nobusiness rules are calculated in these objects. Business rules can becomputed solely by the application.

[0028] Blocks 108 to 110 further illustrate the roles of the each of thethree generated sections of code. Block 108 shows the generated storedprocedures 105. The stored procedures 105 of block 108 communicate viathe generated XML configuration files of XML code 106, as shown by block109, with the application. The generated value objects 107, for exampleJavaBeans, complete the databinding by providing the final mapped datain a form suitable for use in the run-time object oriented environmentof the application, as shown by block 110. Thus the stored procedures ofblock 108 are “connected” to the value objects of block 110 by thegenerated XML middle layer 109.

[0029] The Run-Time Component of the Framework:

[0030]FIG. 3 shows the run-time component 200 of the framework. Itillustrates the method by which the code generated by the codegeneration component is used during the running (run-time) ofapplication. Computations of business rules 205 are done as part ofobjects 206 in the Business Model Tier 207. Information (data) exchangeis done in Data Access Tier 208.

[0031] Data Access Tier 208 comprises the code generated by the codegenerator. Run-time component 200 integrates the generated code into theapplication. The generated stored procedures 108, generated XML middlelayer 109, and generated value objects 110 are tied together by run-timecomponent 200. Data Access Objects (DAOs) 204 are the simplified linesof code (calls) from the objected oriented application. DAOs 204 arehand written into the application by the developer.

[0032] Run-time component 200 comprises subcomponents 201-204. Caller201 provides interaction to the database via a unique identifier foreach XML binding document. Connection manager 202 provides for physicalconnections to the database. Binder Manager 203 maps the storedprocedures to the value objects during application startup. And, CacheManager 204 can cache store procedure results as list of value objectsin memory for a time for quicker application access.

[0033] Using the inventive framework, the developer starts with anexisting relational data model. Then the tables that will be used topersist the application's data are identified. A table of metadata isgenerated from the list of table names by the code generator from whichthe code generator creates the Load, Save, Delete, LoadList StoredProcedures, the Value Objects (i.e. JavaBeans) and, XML bindingdefinitions. Finally, the developer writes the Data Access Objectsbusiness model interface by-hand in just a few lines of applicationcode.

[0034] The benefits of the inventive framework are manifold. Itgenerates both application code and stored procedures. In the bestpractices of object oriented programming, the stored procedures andvalue objects contain no business logic. And, the business model isde-coupled from the underlying persistence model. There is simplifieddata access interface i.e. load, loadList, save, delete. In the case ofa Java solution, it wraps the clunky Java DataBase Connectivity (JDBC)Stored Procedure interface. And, a very important feature is that theframework significantly reduces error-prone, mundane data access, storedprocedure development. Thus, the development effort gains more time tofocus on the business model code because the data access build effortand cost are now 10% of what they would have been without the inventiveframework. Also, stored procedure logging can be easily implemented inthis framework. And finally, run-time can be improved by built-in memorycaching.

[0035] Furthermore, since routing human coding errors are virtuallyeliminated there is far less required data access testing. As will beshown in the example, the framework is easily integrated into standaloneJava applications or J2EE enterprise applications.

[0036] While the inventive framework can be adapted to allknown-business class object oriented languages and commercial databasesystems, the embodiment of the example was coded using Java (JavaBean,JDBC, Reflection), XML, XML schema, Castor, Sybase database, ApacheCommons, and Apache Log4J.

[0037] Part II:

An Example Framework in Java and Sybase with an XML Middle Layer

[0038] The embodiment described here is called SPBinder. SPBinder is alightweight Stored Procedure data-binding framework for Java. Its mainfunction is to significantly reduce the coding effort required to calldatabase Stored Procedures by encapsulating the clunky Java DataBaseConnectivity (JDBC) interface and providing a generic configurable meansof persisting/retrieving Java objects to/from a relational database. Themain advantage of encapsulating the data model is that it purely dealswith persistence and retrieval and therefore becomes de-coupled from thebusiness model. This means the developer can concentrate on implementinga complex business object model without directly impacting theunderlying persistence model. The SPBinder framework has two maincomponents—a code generation component for developers called GenUtilsand run-time component called SPBinder.

[0039] GenUtils

[0040] The GenUtils component is set of code generation utilities thattake a list of database table names (new or legacy) as input andextracts the table metadata directly from a database where the tablesare defined to generate associated, JavaBeans (Value Objects), storedprocedures (loadList, load, save and delete), and XML data-bindingdefinition documents.

[0041] The XML data-binding document (see Appendix for schema) describesthe relationship between stored procedure input/output parameters,resultsets and associated JavaBeans (Value Objects). This means that atrun-time, Stored Procedure input parameters can be mapped directly fromJava Object attributes, and stored procedure resultsets can be mapped toa list of Java Objects.

[0042] Example of XML Data-Binding Definition Document: <?xmlversion=“1.0” encoding=“UTF-8”?> <!-- edited with XML Spy v4.0.1 U(http://www.xmlspy.com) by John Mangan (JPMorganChase) --> <!--SampleXML file generated by XML Spy v4.0.1 U (http://www.xmlspy.com)--><StoredProcBinder> <StoredProcs jdbcConnectionResource=“com.jpmorgan.eqeis.spbinder.database.test.jdbc_conn”> <StoredProcname=“sp_who” id=“Who”> <InputParams> <Param column=“ ®loginname”property=“name” javaType=“java.lang.String”/> </InputParams><ResultSetParams model=“com.jpmorgan.eqeis.spbinder.database.test.IdObject”> <Paramcolumn=“spid” property=“id”/> <Param column=“loginname”property=“name”/> </ResultSetParams> </StoredProc> </StoredProcBinder>

[0043] SPBinder

[0044] SPBinder is the run-time component, which uses the XML bindingdocument and JavaBean (Value Objects) and encapsulates communication tothe database via JDBC.

[0045] It consists of four main components, SPBinderManager,SPConnection, SPCaller and SPCacheManager.

[0046] SPBinderManager

[0047] SPBinderManager normally reads in the XML binding document onceduring application start-up and builds a data-binding mapping in memoryusing the unique identifier to reference each Stored Procedure andassociated data-binding JavaBean (Value Object). This unique identifiercan then be used by SPCaller to do the actual database access.

[0048] Example: static SPBinderManager spBM =SPBinderManager.getInstance( ); spBM.loadXMLConfigResource(“com/jpmorgan/eqeis/spbinder/database/test/SPBindings.xml”);

[0049] SPConnection

[0050] The SPConnection component handles the physical databaseconnections and associated transaction management. It can also handleconnection pooling in a J2EE environment.

[0051] SPCaller

[0052] SPCaller uses the unique identifier defined in the XML bindingdocument to access the database and load, save or delete data. The datais automatically extracted from or populated in the JavaBean (ValueObjects) where appropriate. Using the XML definition above, the examplebelow shows how to load a list of users and their spids (Sybase processId's):

[0053] Example: try { SPCaller spCaller = new SPCaller( ); List list =spCaller.loadList (“Who”, null); // execute sp_who and returns a list ofspids & names Iterator iter = list.iterator ( ); while (iter.hasNext( )) { IdObject idObj = (IdObject) iter.next ( );System.out.println(“Spid:” + idObj.getId( ) + “ Name: ” + idObj.getName( )); } } catch (SPBinderException e) {System.error.println(e.getMessage ( )); }

[0054] The SPCaller component has built-in logging, which can be used totrace Stored Procedure calls, input parameters and resultsets.

[0055] SPCacheManager

[0056] SPCacheManager is a component, which manages a read-onlytime-expiry cache of list based data. This can be used to cache StoredProcedure resultsets as lists of JavaBean (Value Beans) in memory for aperiod of time. The time to expire variable can be configured per StoredProcedure in the XML data-binding document.

Appendix

[0057] StoredProcBinder.xsd <?xml version=“1.0” encoding=“UTF-8”?> <!--edited with XML Spy v4.0.1 U (http://www.xmlspy.com) by John Mangan(JPMorganChase) --> <!--W3C Schema generated by XML Spy v3.5 NT(http://www.xmlspy.com)--> <xs:schemaxmlns:xs=“http://www.w3.org/2001/XMLSchema”elementFormDefault=“qualified” attributeFormDefault=“unqualified”><xs:annotation> <xs:documentation>SPBinder (Stored Proc to JavaBeandatabinding definition) Castor generation -com.jpmorgan.eqeis.spbinder.database.xml</xs:documentation></xs:annotation> <xs:element name=“StoredProcBinder”> <xs:complexType><xs:sequence> <xs:element ref=“StoredProcs” minOccurs=“0”/></xs:sequence> </xs:complexType> </xs:element> <xs:elementname=“StoredProcs”> <xs:complexType> <xs:sequence> <xs:elementref=“StoredProc” minOccurs=“0” maxOccurs=“unbounded”/> </xs:sequence><xs:attribute name=“jdbcConnectionResource” type=“xs:string”use=“required”/> </xs:complexType> </xs:element> <xs:elementname=“StoredProc”> <xs:complexType> <xs:sequence> <xs:elementname=“InputParams” type=“Params” minOccurs=“0”/> <xs:elementname=“ResultSetParams” type=“Params” minOccurs=“0”/> </xs:sequence><xs:attribute name=“name” type=“xs:string” use=“required”/><xs:attribute name=“id” type=“xs:string” use=“optional”/> <xs:attributename=“jdbcConnectionResource” type=“xs:string” use=“optional”/><xs:attribute name=“cacheable” type=“xs:boolean” use=“optional”/><xs:attribute name=“cachetimeout” type=“xs:integer” use=“optional”/></xs:complexType> </xs:element> <xs:complexType name=“Params”><xs:sequence> <xs:element ref=“Param” minOccurs=“0”maxOccurs=“unbounded”/> </xs:sequence> <xs:attribute name=“model”type=“xs:string” use=“optional”/> </xs:complexType> <xs:elementname=“Param”> <xs:complexType> <xs:attribute name=“column”type=“xs:string” use=“required”/> <xs:attribute name=“property”type=“xs:string” use=“required”/> <xs:attribute name=“output”type=“xs:boolean” use=“optional”/> <xs:attribute name=“value”type=“xs:string” use=“optional”/> <xs:attribute name=“javaType”type=“xs:string” use=“optional”/> </xs:complexType> </xs:element></xs:schema>

We claim:
 1. A method of communicating between an application and adatabase comprising the steps of: providing a database; providing anapplication; providing a databinding framework; generating databindingfiles to bind the database data to the application program; andproviding a run-time component to integrate the generated databindingfiles into the application when the application is run.
 2. The method ofclaim 1 wherein providing the database comprises providing a relationaldatabase.
 3. The method of claim 1 wherein providing the databasecomprises providing a relational Sybase database.
 4. The method of claim1 wherein providing the database comprises providing a relationalMicrosoft SQL Server database.
 5. The method of claim 1 whereinproviding the database comprises providing a relational Oracle database.6. The method of claim 1 wherein providing the application comprisesproviding an application written in an object oriented language.
 7. Themethod of claim 1 wherein providing the application comprises providingan application written in Java.
 8. The method of claim 1 whereinproviding the application comprises providing an application written inC++.
 9. A method of databinding a relational database to an applicationcomprising the steps of: providing a relational database residing on atangible computer readable medium; providing an application written inan object oriented computer language residing on the tangible computermedium; providing a databinding framework; identifying tables to persistdata in the database; generating a table of metadata from the identifiedtables; generating stored procedures from the metadata; generating ValueObjects (VO) from the metadata; generating an XML binding definitionfrom the metadata; providing Data Access Objects (DAO); and running theapplication with a run-time component that integrates the storedprocedures, VOs, XML binding definitions and DAOs.
 10. The method ofclaim 9 wherein generating the Value Objects comprises generatingJavaBeans.
 11. The method of claim 9 wherein providing the relationaldatabase comprises providing a relational Sybase database.
 12. Themethod of claim 9 wherein providing the relational database comprisesproviding a relational Microsoft SQL server database.
 13. The method ofclaim 9 wherein providing the relational database comprises providing arelational Oracle database.
 14. The method of claim 9 wherein providingthe application written in an object oriented computer languagecomprises providing an application written in Java.
 15. The method ofclaim 9 wherein providing the application written in an object orientedcomputer language comprises providing an application written in C++. 16.The method of claim 9 wherein providing the databinding frameworkcomprises providing an SPBinder framework.
 17. The method of claim 9wherein running the application with a run-time component comprisesrunning the application with a run-time component comprising thesubcomponents of SPBinder Manager, SPConnection, SPCaller, andSPCacheManager.